package com.example.homeworkmutualevaluation.Dao;

import com.example.homeworkmutualevaluation.Entity.PrimaryKey.SubmitPK;
import com.example.homeworkmutualevaluation.Entity.Submit;
import org.apache.ibatis.annotations.Param;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface SubmitDao extends JpaRepository<Submit, SubmitPK> {
    Submit findBySphoneAndHomeworkid(String sphone, int homeworkid);
    List<Submit> findAllBySphone(String sphone);
    @Query(value = "SELECT sphone FROM submit WHERE `homeworkid`=?1", nativeQuery = true)
    List<String> findAllSubmitter(int homeworkid);
    @Procedure(procedureName = "count_score")
    void countScores(@Param("hid") int homeworkid);
    @Query(value = "SELECT `sphone` FROM submit WHERE `homeworkid`=?1 AND `evnumber`=(SELECT MIN(`evnumber`) FROM submit WHERE `homeworkid`=?1)", nativeQuery = true)
    List<String> getMostNeededSubmitters(int homeworkid);

    @Query(value = "SELECT content FROM submit, task WHERE " +
            "submit.`homeworkid`=task.`homeworkid` AND submit.`sphone`=task.`submitter` AND task.`id`=?1", nativeQuery = true)
    String getContentByTaskid(int taskid);
}
